<?php

namespace Zwwl2016\System;

use Illuminate\Console\Command;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;


class Migration extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'system:repo:migration {data}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'sql migration for commands';



    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }


    /**
     *
     * @output String error_code 错误码;
     * @output String error_message 错误信息;
     * @return bool
     * @throws \ReflectionException
     */
    public function handle()
    {
        $inputFileType = 'Xlsx';
        $inputFileName = $this->argument('data');
        if(!is_file($inputFileName))
        {
            echo 'file not exists!';
            die();
        }
        $segmentFloder="cmd_segment";
        if(!is_dir($segmentFloder))
        {
            mkdir($segmentFloder,0777,true);
        }
        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $tables=array();
        $table='';
        $column='';

        foreach($sheetData as $line)
        {
            if(!empty($line['A']))
            {
                $table=$line['A'];
                $tables[$table]=array();
            }
            if(!empty($line['B']))
            {
                $column=$line['B'];
                $tables[$table][$column]=array();
            }
            $tables[$table][$column]['type']='String';
            if(!empty($line['C']))
            {
                $tables[$table][$column]['type']=$line['C'];
            }
            $tables[$table][$column]['must']='否';
            if(!empty($line['D']))
            {
                $tables[$table][$column]['must']=$line['D'];
            }
            if(!isset($tables[$table][$column]['comment']))
            {
                $tables[$table][$column]['comment']='';
            }
            else
            {
                $tables[$table][$column]['comment'].=" ";
            }
            $comment=$line['E'];
            if(!empty($line['F']))
            {
                $comment.=" ".$line['F'];
            }
            $tables[$table][$column]['comment'].=$comment;
        }

        $sql_create='';
        $sql_alter='';

        foreach($tables as $table=>$columninfo)
        {

            $table_key=$table;
            $table="la_".$table;
            $sql_create.='create table if not exists `'.$table.'`($$$$'." ";
            $sql_create.='  `id` int(11) signed NOT NULL AUTO_INCREMENT,'." ";
            $sql_comment1="* @input Object $table_key $$$$";
            $sql_comment1.="* @output Object $table_key $$$$";
            $sql_comment1.="-----------------------------------------------$$$$";
            $sql_comment2='$data = $this->argument("data");$$$$';
            $sql_comment2.='$data = json_decode($data,true);$$$$';
            $sql_comment2.="//输入-单例$$$$";
            $sql_comment3="//输出-单例$$$$";
            $sql_comment3.='$data["error_code"]=@$result->errcode;$$$$';
            $sql_comment3.='$data["error_message"]=@$result->errmsg;$$$$';
            $sql_comment4="//输出-数组$$$$";
            $sql_comment4.='$array=[];$$$$';
            $sql_comment5="//输入-数组$$$$";
            $sql_comment5.="\$$table_key=@\$data[\"$table_key\"]; $$$$";
            $sql_comment5.="foreach(\$$table_key as \$item)";
            $sql_comment5.="{ $$$$";
            foreach($columninfo as $column=>$info)
            {
                $info['comment']=str_replace("'","\\'",$info['comment']);
                $info['comment']=str_replace("\\",'\\\\',$info['comment']);
                if($column=='id'
                    || $column=='created_at'
                    || $column=='updated_at'
                    || $column=='deleted_at'
                )
                {
                    continue;
                }
                $sentence="varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '".$info['comment']."'";
                if($info=='Integer')
                {
                    $sentence="int(11) DEFAULT '0' COMMENT '".$info['comment']."'";
                }
                else if($info=='Boolean')
                {
                    $sentence="tinyint(4) DEFAULT '0' COMMENT '".$info['comment']."'";
                }
                else if($info=='Text')
                {
                    $sentence="text DEFAULT null COMMENT '".$info['comment']."'";
                }
                $sql_create.='  `'.$column.'` '.$sentence.',$$$$';
                $sql_alter.="alter table `".$table.'` add column `'.$column.'` '.$sentence.''."\n";
                $comment_head=$info['comment'];


                $sql_comment2.="\$$column=@\$data[\"$table_key\"][\"$column\"]; //$comment_head $$$$";
                $sql_comment3.="\$data[\"$table_key\"][\"$column\"]=@\$result->$column;$$$$";
                $sql_comment4.="\$array[\"$column\"]=@\$item->$column;$$$$";
                $sql_comment5.="    \$$column=@\$item[\"$column\"]; //$comment_head $$$$";
            }
            $sql_create.="  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',$$$$";
            $sql_create.="  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',$$$$";
            $sql_create.="  `deleted_at` datetime NULL COMMENT '删除时间',$$$$";
            $sql_alter.="alter table `".$table."` add column `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'"."\n";
            $sql_alter.="alter table `".$table."` add column `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间'"."\n";
            $sql_alter.="alter table `".$table."` add column `deleted_at` datetime NULL COMMENT '删除时间'"."\n";
            $sql_comment2.="\$created_at=@\$data[\"$table_key\"][\"created_at\"];//创建时间$$$$";
            $sql_comment2.="\$updated_at=@\$data[\"$table_key\"][\"updated_at\"];//更新时间$$$$";
            $sql_comment2.="\$deleted_at=@\$data[\"$table_key\"][\"deleted_at\"];//删除时间$$$$";
            $sql_comment2.="-----------------------------------------------$$$$";
            $sql_comment5.="    \$created_at=@\$item[\"created_at\"];//创建时间$$$$";
            $sql_comment5.="    \$updated_at=@\$item[\"updated_at\"];//更新时间$$$$";
            $sql_comment5.="    \$deleted_at=@\$item[\"deleted_at\"];//删除时间$$$$";
            $sql_comment5.="}$$$$";
            $sql_comment5.="-----------------------------------------------$$$$";

            $sql_comment3.="echo json_encode(\$data);$$$$";
            $sql_comment3.="-----------------------------------------------$$$$";
            $sql_comment4.="\$data[\"$table_key\"][]=\$array;$$$$";
            $sql_comment4.="-----------------------------------------------$$$$";
            $commentContent=str_replace('$$$$',"\n",$sql_comment1.$sql_comment2.$sql_comment5.$sql_comment3.$sql_comment4);
            file_put_contents($segmentFloder.DIRECTORY_SEPARATOR.$table_key.".txt",$commentContent);
            $sql_create.='   PRIMARY KEY (`id`) USING BTREE $$$$';
            $sql_create.='  )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'."\n";
        }
        $file_sql="";
        $skip=false;
        foreach(explode("\n",$sql_create) as $line)
        {
            if(empty($line))
            {
                continue;
            }
            $line=str_replace('$$$$',"\n",$line);
//            if(!$skip&&class_exists('DB')){
//                try
//                {
//                    \DB::statement($line);
//                }
//                catch (\Exception $e)
//                {
//                    if($e->getCode()==2002)
//                    {
//                        $skip=true;
//                    }
//                }
//            }
            $file_sql.=$line.";"."\n";
        }

        foreach(explode("\n",$sql_alter) as $line)
        {
            if(empty($line))
            {
                continue;
            }
            $line=str_replace('$$$$',"\n",$line);
//            if(!$skip&&class_exists('DB')){
//                try
//                {
//                    \DB::statement($line);
//                }
//                catch (\Exception $e)
//                {
//                    if($e->getCode()==2002)
//                    {
//                        $skip=true;
//                    }
//                }
//            }
            $file_sql.=$line.";"."\n";
        }
        file_put_contents(dirname($inputFileName).'/doc.sql',$file_sql);
        return true;
    }
}